import pandas as pd
import openpyxl
import win32com.client as win32
import os
import time

def unmerge_and_fill_cells(worksheet):
    all_merged_cell_ranges = list(
        worksheet.merged_cells.ranges
    )

    for merged_cell_range in all_merged_cell_ranges:
        merged_cell = merged_cell_range.start_cell
        worksheet.unmerge_cells(range_string=merged_cell_range.coord)

        for row_index, col_index in merged_cell_range.cells:
            cell = worksheet.cell(row=row_index, column=col_index)
            cell.value = merged_cell.value
def unmerge_cell(filename):
    wb = openpyxl.load_workbook(filename)
    for sheet_name in wb.sheetnames:
        sheet = wb[sheet_name]
        unmerge_and_fill_cells(sheet)
    filename = filename.replace(".xlsx", "_temp.xlsx")
    wb.save(filename)
    wb.close()

current_path = os.getcwd()


if __name__ == '__main__':                                  #要要求他们把输入的数据改成xlsx
    file_path = current_path + "\\张总.xlsx"  
    xls = pd.ExcelFile(file_path)  				#读入xlsx文件属性
    sheet_names = xls.sheet_names  
  
    # 将每个sheet转换为DataFrame  
    dfs = {sheet_name: pd.read_excel(file_path, sheet_name=sheet_name) for sheet_name in sheet_names}  
  
    # 将所有DataFrame拼接成一个DataFrame  
    combined_df = pd.concat(dfs.values(), ignore_index=True)  
 
    df_filtered = combined_df.dropna(subset=['克拉数']) 		#以克拉数为空删除无用记录

    
    df_filtered2 = df_filtered.copy()  # 确保df_filtered是一个独立的副本  
    df_filtered2['克拉数'] = pd.to_numeric(df_filtered2['克拉数'], errors='coerce')
    df_filtered3 = df_filtered2.dropna(subset=['克拉数']) 
    dfx = df_filtered3[['编号','粒数','克拉数','单价','金额']]
    fill_next = dfx['编号'].isnull() | (dfx['编号'] == '') # 初始化一个序列来存储上一行的非空名字
    prev_name = None  # 初始化一个序列来存储上一行的非空名字
    
    df_reset_index = dfx.reset_index(drop=True) 
    num_rows = len(df_reset_index.index) 
    # 遍历DataFrame的每一行（包括最后一行），并检查是否需要填充名字  
    for index, row in df_reset_index.iloc[:num_rows + 2].iterrows():  #用的索引来标记行的，因此要+2
        if fill_next.iloc[index]:          # 如果当前行的名字为空，则复制上一行的非空名字  
            df_reset_index.at[index, '编号'] = prev_name  
        else:              # 如果当前行的名字非空，则更新prev_name为当前行的名字  
            prev_name = row['编号']  
    df_reset_index.columns = ['货单编号', '粒数','重量','单价','金额']  

#判断并且删除不是数字的行
    output = df_reset_index.reset_index(drop=True)
    not_numbers = output['金额'].apply(lambda x: not isinstance(x, (int, float)))  
  
    # 使用布尔索引来过滤出 '金额' 列是数字的行  
    df_filter22 = output[~not_numbers]  

    
    output = df_reset_index.copy()
    output.loc[:, '金额'] = output['金额'] * 1.15
    output.to_excel('取张总副石金额.xlsx', index=False)
    output.to_excel('已取张总副石金额.xlsx', index=False)
    